package com.mililoan.common.utils.excel;

import java.awt.Component;
import java.awt.List;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.io.OutputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.Region;

/**
 * 查询页，数据表转换为excel表下载
 * 将二维数据转换为Excel表
 * @author hozen 2014-6-26
 */
public class ExcelBPO {


	public ExcelBPO() {
		// TODO Auto-generated constructor stub
	}
	
	/**
	 * 生成excel
	 * @since hozen 2014-6-26 
	 * @param dataList
	 * @param os
	 * @throws Exception
	 */
    public static void makeExcel(ArrayList dataList,OutputStream os) throws Exception{

        HSSFWorkbook wb = new HSSFWorkbook(); //建立新HSSFWorkbook对象
        HSSFSheet sheet = wb.createSheet("new sheet"); //建立新的sheet对象
        short rowNum = 0;
        short colNum = 0;
        HSSFRow row = null;
        HSSFCell cell = null;
        
        wb.setSheetName(0, "sheet1");
        
        //HSSFFont fontdefault = wb.createFont();
        //fontdefault.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
        //fontdefault.setFontHeightInPoints((short) 11);

        row = sheet.createRow(rowNum);
        colNum = 8;
        String text = "";
        
		for(int i=0;i<dataList.size();i++){
			rowNum = (short)i;
            colNum = 0;
            row = sheet.createRow(rowNum);
            ArrayList lineList = (ArrayList) dataList.get(i);
			for(int j=0;j<lineList.size();j++){
				
	            text = (String)lineList.get(j);
	            
	            cell = row.createCell(colNum++);
	            setCell(cell, null, text);
			}
		}
        rowNum++;
        rowNum++;
        row = sheet.createRow(rowNum);
        colNum = 0;
        
        wb.write(os);
    }

    /**
     * 设置单元格的值
	 * @since hozen 2014-6-26 
     * @param cell
     * @param style
     * @param value
     */
    private static void setCell(HSSFCell cell,HSSFCellStyle style,String value){
      cell.setCellType(HSSFCell.CELL_TYPE_STRING);
      //cell.setEncoding(HSSFCell.ENCODING_UTF_16);
      cell.setCellValue(value);
      if (style != null){
        cell.setCellStyle(style);
      }
   }
	
	
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		// TODO Auto-generated method stub

	}

    //setCell(cell, styledefault, text);
    
	//=(String)(list.get(i).get(j));

    //File  file = new File(GN.reportSrc + "/txn_dtl_report.xls");
    //FileOutputStream os = new FileOutputStream(file);
    /*
    //对表格设置值
    for (int i = 0; i < dataList.length; i++) {
        colNum = 0;
        row = sheet.createRow(rowNum);

        //set 编号
        text = String.valueOf(i + 1);
        cell = row.createCell(colNum);
        setCell(cell, null, text);
        //setCell(cell, styledefault, text);

        colNum++;
        //set 订单号
        text = String.valueOf("123");
        cell = row.createCell(colNum);
        setCell(cell, null, text);
        //setCell(cell, styledefault, text);

    }*/
	/*
    HSSFCellStyle styledefault = wb.createCellStyle();
    styledefault.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
    styledefault.setBorderLeft(HSSFCellStyle.BORDER_THIN); //左边框
    styledefault.setBorderRight(HSSFCellStyle.BORDER_THIN); //右边框
    styledefault.setBorderTop(HSSFCellStyle.BORDER_THIN); //上边框
    styledefault.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER); //垂直居中
    styledefault.setFont(fontdefault);
    rowNum++;
*/
    /*
    HSSFRow row = sheet.createRow(rowNum);;
    HSSFCell cell = row.createCell((short) 0);;

    HSSFFont font = wb.createFont();
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font.setFontHeightInPoints((short) 25);
    HSSFCellStyle style = wb.createCellStyle();

    style.setFont(font);
    style.setAlignment(style.ALIGN_CENTER);

    String text = "团购订单查询列表";
    setCell(cell, style, text);
    sheet.addMergedRegion(new Region(0, (short) 0, 0, (short) 9));
    //合计用样式
    HSSFFont font_totle = wb.createFont();
    font_totle.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    font_totle.setFontHeightInPoints((short) 9);
    HSSFCellStyle style_totle = wb.createCellStyle();
    style_totle.setFont(font_totle);
    style_totle.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
    style_totle.setBorderLeft(HSSFCellStyle.BORDER_THIN); //左边框
    style_totle.setBorderRight(HSSFCellStyle.BORDER_THIN); //右边框
    style_totle.setBorderTop(HSSFCellStyle.BORDER_THIN); //上边框

    //第二行
    rowNum++;
    row = sheet.createRow(rowNum);
    cell = row.createCell((short) 0);
    String text1 = saleinfo.getName();

    HSSFFont font1 = wb.createFont();
    font1.setBoldweight(HSSFFont.BOLDWEIGHT_NORMAL);
    font1.setFontHeightInPoints((short) 9);
    HSSFCellStyle style1 = wb.createCellStyle();
    style1.setFont(font1);
    style1.setAlignment(style1.ALIGN_CENTER);

   setCell(cell, style1, text1);
   sheet.addMergedRegion(new Region(1, (short) 0, 1, (short) 9));

*/

//  String filePath = "D://";
//  String dateString = new SimpleDateFormat("yyyyMMdd").format(new Date());
//  String fileName = "zhsj_" + dateString + ".xls";
//  String filePathName = filePath + File.separator + fileName;
//  File file = new File(filePathName);
//  FileOutputStream os = new FileOutputStream(file);
//
//  wb.write(os);
//  os.close();

    //********set head row*********//
/*
    style = wb.createCellStyle();
    style.setAlignment(style.ALIGN_RIGHT);

    text = "创建时间：" + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date());
    cell = row.createCell(colNum);
    setCell(cell, style, text);
    sheet.addMergedRegion(new Region(rowNum, (short) 0, rowNum, (short) 9));


    String dateString = new SimpleDateFormat("yyyyMMdd").format(new Date());
    String fileName = saleBuyC.getSaleid()+"_"+dateString + ".xls";

    Fentity fentity = new Fentity(FstorerConf.SaleExcel.FSTORER_TYPE, saleBuyC.getSaleid(),fileName);*/
    
	
	/*	
		//title 标题，con 条件，data 数据，foot 标底 
		@SuppressWarnings("deprecation")
		public static void arrayToFile(String title, String con, String[] header,  String[][] data, String foot, String fn) throws IOException, AppException{
			HSSFWorkbook wb = new HSSFWorkbook();
			HSSFSheet sheet = wb.createSheet();
			wb.setSheetName(0, title);
			
			HSSFCell cellStyle = wb.createCellStyle();
			cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			
			HSSFCellStyle headerStyle = wb.createCellStyle();
			headerStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			headerStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			headerStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			headerStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			headerStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			headerStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			headerStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);         
			headerStyle.setFillForegroundColor(HSSFColor.LIGHT_YELLOW.index); 
			
			HSSFCellStyle titleStyle = wb.createCellStyle();
			titleStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
			titleStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			titleStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			titleStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			titleStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			titleStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
			
			HSSFCellStyle conStyle = wb.createCellStyle();
			conStyle.setAlignment(HSSFCellStyle.ALIGN_LEFT);
			conStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			conStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			conStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			conStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			
			HSSFCellStyle footStyle = wb.createCellStyle();
			footStyle.setAlignment(HSSFCellStyle.ALIGN_RIGHT);
			footStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
			footStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
			footStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
			footStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
			
			HSSFFont cellFont = wb.createFont();
			cellFont.setFontName("黑体");
			cellFont.setFontHeightInPoints((short) 10);
			cellFont.setBoldweight((short) 50); 
			
			HSSFFont titleFont = wb.createFont();
			titleFont.setFontName("黑体");
			titleFont.setFontHeightInPoints((short) 14);
			titleFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
			
			headerStyle.setFont(cellFont);                
			cellStyle.setFont(cellFont);
			titleStyle.setFont(titleFont);
			conStyle.setFont(cellFont);
			footStyle.setFont(cellFont);
			
			HSSFRow row = null;
			HSSFCell cell = null;         

			String colName = null;
			int rowNum = 0;
			int cols = (header!=null && header.length>0)?header.length:data[0].length;
			
			if(!(title==null||title.equals(""))){
				row = sheet.createRow(rowNum++);
				for(int i=0; i<cols; i++){
					cell = row.createCell((short)i);
					cell.setCellStyle(titleStyle);
					if(i==0) cell.setCellValue(new HSSFRichTextString(title));
				}
				sheet.addMergedRegion(new Region((short)(rowNum-1),(short)0, (short)(rowNum-1), (short)(cols-1)));
				sheet.setHorizontallyCenter(true);
			}

			if(!(con==null||con.equals(""))){
				row = sheet.createRow(rowNum++);
				for(int i=0; i<cols; i++){        		
					cell = row.createCell((short)i);
					cell.setCellStyle(conStyle);
					if(i==0)cell.setCellValue(new HSSFRichTextString(con));
				}
				sheet.addMergedRegion(new Region((short)(rowNum-1),(short)0, (short)(rowNum-1), (short)(cols-1)));
				sheet.setHorizontallyCenter(true);
			}
			
			if(header != null && header.length>0){
				row = sheet.createRow(rowNum++);
				for(int i=0; i<cols; i++){
					colName = header[i];
					cell = row.createCell((short) i);
					cell.setCellStyle(headerStyle);
					cell.setCellValue(new HSSFRichTextString(colName));
				}
			}

			for(int i=0; i<data.length; i++){
				row = sheet.createRow(rowNum++);
				for(int j=0; j<cols; j++){
					cell = row.createCell((short) j);
					cell.setCellStyle(cellStyle);
					cell.setCellValue(new HSSFRichTextString(data[i][j]));
				}
			}

			if(!(foot==null||foot.equals(""))){
				row = sheet.createRow(rowNum++);
				for(int i=0; i<cols; i++){
					cell = row.createCell((short)i);
					cell.setCellStyle(footStyle);
					if(i==0)cell.setCellValue(new HSSFRichTextString(foot));
				}
				sheet.addMergedRegion(new Region((short)(rowNum-1),(short)0, (short)(rowNum-1), (short)(cols-1)));
				sheet.setHorizontallyCenter(true);
			}

			for(int i=0; i<cols; i++){
				sheet.autoSizeColumn((short)i);
			}
			
			String fp = GV.rootSrc + GV.expFilePath + File.separator;
			File file = new File(fp + fn);
			if(file.exists()){
				if(!file.delete()){
					throw new AppException("删除存在的文件失败！");
				}
			}else{
				File path = file.getParentFile();//取得文件路径
			    if (!path.exists()) {//若文件路径不存在
			      path.mkdirs();//创建路径
			    }
			}
			OutputStream os = new FileOutputStream(file);
			wb.write(os);
			os.close();
		}
	*/
		
}
